
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spDeliveredOrdersGetListByBulkOrder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spDeliveredOrdersGetListByBulkOrder]
GO

	
CREATE PROCEDURE spDeliveredOrdersGetListByBulkOrder
	@BulkOrderID int
AS

SELECT
	[DeliveredOrders].[DeliveredOrderID],
	Offices.[OfficeID],
	Offices.OfficeName as OfficeName,
	CAST(ssr.PONO_Supplier as varchar) as [SupplierCode],
	CAST(ssr.PONo_Number as varchar) as [OrderNo],
	SUBSTRING(CAST(DATEPART(yy, ssr.[OrderDate]) as varchar), 3, 2) [YY],
	ssr.[OrderDate] OrderDate,
	[QtyIssued],
	[DateIssued],
	[ProductCode],	
	[BulkOrderID],
	ssr.CustomerOrLocation as [CustomerName],
	[POID],
	ssr.[ShippingDate] ShippedDate,
	ssr.REQ,
	ssr.SalesOrder
FROM
	[DeliveredOrders] LEFT JOIN Offices ON [DeliveredOrders].[OfficeID] = Offices.[OfficeID]
	LEFT JOIN ShippingStatusReport ssr ON ssr.ID = [DeliveredOrders].POID
WHERE [DeliveredOrders].BulkOrderID = @BulkOrderID
ORDER BY [OrderDate] DESC

GO

	
 